from datetime import datetime
from typing import Tuple, List

from pydantic import BaseModel
from sqlalchemy import Column, Integer, String, select, func, DateTime
from sqlalchemy.orm import declarative_base

from utils.drivers.database.engine import AsyncSession
from utils.enums import Language
from utils.exception import AppException, StatusCode

Base = declarative_base()


class PageImageModel(BaseModel):
    id: int
    key: str
    sort: int
    title: str
    create_time: datetime
    update_time: datetime


class PageImages(Base):
    __tablename__ = "page_images"

    id = Column(Integer, primary_key=True, autoincrement=True)
    key = Column(String(255))
    sort = Column(Integer)
    title = Column(String(255))
    title_en = Column(String(255))
    create_time = Column(DateTime, default=datetime.now())
    update_time = Column(DateTime, default=datetime.now(), onupdate=datetime.now())

    @classmethod
    async def get(cls, language: Language, key: str, page: int = 1, page_size: int = 12) -> tuple[list[PageImageModel], int]:
        stmt = select(cls).where(cls.key == key).order_by(cls.sort.asc(), cls.id.asc())
        try:
            async with AsyncSession() as session:
                async with session.begin():
                    count_stmt = select(func.count()).select_from(stmt.alias("subquery"))
                    count = await session.execute(count_stmt)
                    count = count.scalar()

                    page_images = await session.execute(stmt.offset((page - 1) * page_size).limit(page_size))
                    page_images = page_images.scalars().all()
        except Exception as e:
            raise AppException(StatusCode.QueryAboutImagesError, detail=str(e))

        return [PageImageModel(
            id=img.id,  # type: ignore
            key=img.key,  # type: ignore
            sort=img.sort,  # type: ignore
            title=img.title if language == Language.CN else img.title_en,  # type: ignore
            create_time=img.create_time,  # type: ignore
            update_time=img.update_time,  # type: ignore
        ) for img in page_images], count
